package com.beyond.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.beyond.entity.TableConstruct;
import com.beyond.util.Constant;
import com.beyond.util.FileUtil;


public class DataQueryDao {
	public static Connection conn = null;
	
	/**
	 * 获取数据库连接对象
	 * @return
	 */
	public Connection getConn() {  
		String driver = "jdbc.driverClassName";  
        String url = "jdbc.url";  
        String username = "jdbc.username";  
        String password = "jdbc.password";  
        FileUtil fileUtil = new FileUtil();
        try {  
            Class.forName("oracle.jdbc.driver.OracleDriver");  
            conn = DriverManager.getConnection(fileUtil.getPropertyVal(url), 
            		fileUtil.getPropertyVal(username), 
            		fileUtil.getPropertyVal(password));  
        }
        catch (ClassNotFoundException e) {  
            e.printStackTrace();  
        }  
        catch (SQLException e) {  
            e.printStackTrace();  
        }
        return conn;  
    }
	
	/**
	 * 获取表的记录数
	 * @param tabName[]
	 * @return
	 */
	public int getTabCount(String tabName){
		String sql = "select count(1) as num from "+tabName;
		Connection conn = this.getConn();
		ResultSet rsset = null;
		int cnt1 = 0;
		try {
			rsset = conn.createStatement().executeQuery(sql);
			rsset.next();
			cnt1 = rsset.getInt("num");
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				if(rsset != null){
					rsset.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if(conn != null){
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return cnt1;
	}
	
	/**
	 * 根据表名获取表结构信息
	 * @param tableName
	 * @return
	 */
	public List<TableConstruct> getColumns(String tableName){
		List<TableConstruct> list = new ArrayList<TableConstruct>();
		ResultSetMetaData rsMetaData = null;
		Connection conn = this.getConn();//.getConnection();
		String sql = "select * from "+tableName;
		StringBuilder dimention = new StringBuilder();
		StringBuilder target = new StringBuilder();
		String columnName = ""; 
		try {
			rsMetaData = conn.prepareStatement(sql).executeQuery().getMetaData();
			for (int i = 0; i < rsMetaData.getColumnCount(); i++) {
				TableConstruct obj = new TableConstruct();
				obj.setColumnLen(rsMetaData.getColumnDisplaySize(i+1));
				obj.setColumnName(rsMetaData.getColumnName(i+1));
				obj.setDataType(rsMetaData.getColumnTypeName(i+1));
				obj.setDbDataType(rsMetaData.getColumnClassName(i+1));
				list.add(obj);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				if(conn != null){
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
		}
		return list;
	}
	
	/**
	 * 根据表名，获取表字段名称，包括维度和指标，获取维度"dimention",获取指标"target"
	 * @param tableName
	 * @return
	 */
	public Map<String,String> getColumnName(String tableName){
		Map<String,String> map = Constant.map;
		ResultSetMetaData rsMetaData = null;
		Connection conn = this.getConn();
		String sql = "SELECT * FROM "+tableName;
		StringBuilder dimention = new StringBuilder();
		StringBuilder target = new StringBuilder();
		String columnName = ""; 
		try {
			rsMetaData = conn.prepareStatement(sql).executeQuery().getMetaData();
			for (int i = 0; i < rsMetaData.getColumnCount(); i++) {
				columnName = rsMetaData.getColumnName(i+1);
				if(columnName.contains("0"))
					target.append(columnName).append(",");
				else
					dimention.append(columnName).append(",");
			}
			map.put("dimention",dimention.substring(0,dimention.length()-1));
			map.put("target",target.substring(0,target.length()-1));
		} catch (SQLException e) {
			System.out.println("Exception:"+tableName);
			e.printStackTrace();
		}finally{
			try {
				if(conn != null){
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return map;
	}

	
	/**
	 * 更新表
	 * @param sql
	 */
	public void update(String sql){
		Connection conn = this.getConn();
		try {
			conn.createStatement().executeUpdate(sql);
		} catch (SQLException e) {
			System.out.println(sql);
			e.printStackTrace();
		}finally{
			try {
				if(conn != null){
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * 每列占总记录数的比率
	 * @param tabName
	 * @param cols
	 * @param sql
	 * @param totalCount
	 * @return
	 */
	public Map<String,Double> getTotalRat(String tabName,String cols,String sql,int totalCount){
		/*if("F_PS_GDS05_3G_SAC_H".equals(tabName)){
			System.out.println(tabName);
		}*/
		Map<String,Double> rsMap = new HashMap<String, Double>(); 
		Map<String,Double> map = getResultSet(cols, sql);
		
		for (Map.Entry<String, Double> obj : map.entrySet()) {
			if(totalCount >0)
				rsMap.put(obj.getKey(), obj.getValue()/totalCount);
			else
				rsMap.put(obj.getKey(), 0.0);
		}
		rsMap.put(tabName, Double.valueOf(totalCount));
		
		return rsMap;
	}
	/**
	 * 获取sql查询的结果集
	 * @param cols
	 * @param sql
	 */
	public Map<String,Double> getResultSet(String cols,String sql){
		Connection conn = this.getConn();
		Map<String,Double> map = new HashMap<String,Double>();
		try {
			ResultSet rs = conn.createStatement().executeQuery(sql);
			String[] colArray = cols.split(",");
			while(rs.next()){
				for (String col : colArray) {
					
					if("MINUTE_15_ID".equals(col)){
						continue;
					}else if ("HOUR_ID".equals(col)){ 
						continue;
					}else if("DAY_ID".equals(col)){
						continue;
					}else if("CREATE_TIME".equals(col)){
						continue;
					}else
						map.put(col, rs.getDouble(col));
				}
			}
		} catch (SQLException e) {
			System.err.println("Exception: "+sql);
			e.printStackTrace();
		}finally{
			try {
				if(conn != null){
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return map;
	}
}


















